﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using Zyg.Utility.DBUtility;
using Zyg.SSO.Core.Business;

namespace Zyg.SSO.Core.Providers.DALProviders
{
    public partial class MySqlProvider : DALProvider
    {
        #region Sql Statements

        private static readonly string SqlSelectRoleById = "SELECT `ID`,`Name`,`DateCreated`,`Remark` FROM `Role` WHERE `ID` = @ID; set names utf8;";
        private static readonly string SqlSelectRolesAll = "SELECT `ID`,`Name`,`DateCreated`,`Remark` FROM `Role`; set names utf8;";

        private static readonly string SqlInsertRole = "INSERT INTO `Role` (`ID`,`Name`,`DateCreated`,`Remark`) VALUES (@ID,@Name,@DateCreated,@Remark); set names utf8;";

        private static readonly string SqlDeleteRoleById = "DELETE FROM `Role` WHERE `ID` = @ID; set names utf8;";

        private static readonly string SqlUpdateRoleById = "UPDATE `Role` SET `Name` = @Name,`DateCreated` = @DateCreated,`Remark` = @Remark WHERE `ID` = @ID; set names utf8;";

        #endregion

        public override Zyg.SSO.Core.Business.Role SelectRole(Guid id)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, id);
            MySqlDataReader reader = db.ExecuteSqlReader(SqlSelectRoleById);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Role role = new Role();

                if (!reader.IsDBNull(1)) role.Name = reader.GetString(1);
                if (!reader.IsDBNull(2)) role.DateCreated = reader.GetDateTime(2);
                if (!reader.IsDBNull(3)) role.Remark = reader.GetString(3);

                reader.Close();

                return role;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public override void InsertRole(Zyg.SSO.Core.Business.Role role)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, role.Id);
            db.AddParameter("@Name", MySqlDbType.VarChar, role.Name);
            db.AddParameter("@DateCreated", MySqlDbType.DateTime, role.DateCreated);
            db.AddParameter("@Remark", MySqlDbType.VarChar, role.Remark);

            db.ExecuteSql(SqlInsertRole);
        }

        public override void DeleteRole(Zyg.SSO.Core.Business.Role role)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, role.Id);

            db.ExecuteSql(SqlDeleteRoleById);
        }

        public override void UpdateRole(Zyg.SSO.Core.Business.Role role)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@ID", MySqlDbType.VarChar, role.Id);
            db.AddParameter("@Name", MySqlDbType.VarChar, role.Name);
            db.AddParameter("@DateCreated", MySqlDbType.DateTime, role.DateCreated);
            db.AddParameter("@Remark", MySqlDbType.VarChar, role.Remark);

            db.ExecuteSql(SqlUpdateRoleById);
        }

        public override bool ExistsRole(string name)
        {
            MySqlUtility db = new MySqlUtility();

            db.AddParameter("@Name", MySqlDbType.VarChar, name);

            MySqlDataReader reader = db.ExecuteSqlReader("Select `ID` From `Role` Where `Name` = @Name");

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                reader.Close();

                return true;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return false;
            }
        }

        public override IList<Zyg.SSO.Core.Business.Role> FillRoles()
        {
            IList<Role> results = new List<Role>();

            MySqlUtility db = new MySqlUtility();

            MySqlDataReader reader = db.ExecuteSqlReader(SqlSelectRolesAll);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Role role = new Role();

                    if (!reader.IsDBNull(0)) role.Id = reader.GetGuid(0);
                    if (!reader.IsDBNull(1)) role.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2)) role.DateCreated = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3)) role.Remark = reader.GetString(3);

                    role.MarkOld();

                    results.Add(role);
                }

                reader.Close();
            }

            return results;
        }
    }
}
